Retaining transactions for starting tasks from an LSN
Replicate PostgreSQL-based endpoints provide a Retention period in minutes option that allows you to specify a retention period for processed transactions in the logical replication slot. This can be used together with the Tables are already loaded. Start processing changes from: advanced run option to start a task from a specific Log Sequence Number (LSN). This topic provides general guidelines for calculating the retention period, and setting the LSN.
Understanding how the "Retention period in minutes" endpoint option interacts with the "Start processing changes from" advanced run option
The Retention period in minutes option controls how long PostgreSQL retains processed transactions in the logical replication slot. The Tables are already loaded. Start processing changes from: advanced run option allows you to start a task from a specific LSN, which is useful for Point-in-Time recovery scenarios (or any other scenario that only requires replication of changes from a specific point in time).
These two options work together: the retention period must be sufficient to preserve transactions that you might need to replicate at some point in the future. Transactions that are not retained will not be replicated, regardless of which LSN you specify.
Determining the retention period in minutes
Best practice is to calculate the required retention period based on your recovery requirements and transaction rate:
-
Estimate your average transaction rate. Determine how many transactions your PostgreSQL instance generated per minute during normal operation.
-
Calculate WAL size per minute. Estimate the Write-Ahead Log (WAL) size generated per minute. Consult your PostgreSQL administrator or database monitoring tools to determine this value.
-
Determine your maximum WAL segment size. By default, PostgreSQL WAL segment size is 16 MB. You can verify this by running:
SELECT setting FROM pg_settings WHERE name = 'wal_segment_size';
-
Factor in your recovery window. Set the retention period to cover your required Point-in-Time Recovery window. For example:
- For 24-hour recovery capability: ensure the retention period can preserve 24 hours of changes.
- For 7-day recovery capability: ensure the retention period can preserve 7 days of changes.
-
Account for task downtime. Include buffer time for scenarios where your data task may be paused or stopped. Add extra minutes to your retention period to prevent transactions from being purged while the task is inactive.
Locating an LSN in PostgreSQL
You need to specify an LSN when you want to start a task from a specific point in time. Your DBA should be able to locate the LSN corresponding to the COMMIT from which you want to start the task.
The commit LSN is the LSN of the COMMIT WAL record written for that transaction. Every COMMIT record in WAL contains the XID, so any method that lets you scan WAL records and filter by XID + record type = COMMIT will give you the exact LSN.
| Method | Requires | Returns |
|---|---|---|
| pg_waldump CLI | WAL file access | Commit LSN directly |
| pg_walinspect extension | PG 15+, superuser | Commit LSN via SQL |
| pg_logical_slot_peek_changes | Logical replication slot | LSN of change stream |
| pg_xact_commit_timestamp() | track_commit_timestamp=on | Timestamp only |
The pg_walinspect approach is the most ergonomic SQL-native solution on modern PostgreSQL.
Using an LSN to start a task
After you have identified the COMMIT LSN of the transaction from which you want to start:
- Open the Advanced run options dialog.
- In the Source change position (e.g. SCN or LSN) field, enter the LSN value (for example, 0/3023D50).
- Click OK.
The task will start processing transactions from the specified LSN.